import unittest
import openpyxl
from openpyxl.styles import PatternFill
import pandas
from openpyxl.drawing.image import Image


class OpenpyxlTest(unittest.TestCase):

    # 单纯的创建xlsx文件
    def test_save_file(self):
        wb = openpyxl.Workbook()
        wb.create_sheet('test_case')
        wb.save('E:\\temp\\cases.xlsx')

    # 读取xlsx文件的内容
    def test_read_excel(self):
        wb = openpyxl.load_workbook('E:\\temp\\cases.xlsx')
        sh = wb['Sheet']
        for row in list(sh.rows)[1:]:
            print(row[0].value, row[1].value, row[2].value)
        wb.close()

    # 读取xlsx文件的内容处理并保存到另一个excel中
    def test_read_and_save(self):
        wb1 = openpyxl.Workbook()
        sh1 = wb1.create_sheet('result')
        wb = openpyxl.load_workbook('E:\\temp\\cases.xlsx')
        sh = wb['Sheet']
        for row in list(sh.rows)[1:]:
            temp_row = []
            temp_row.append(row[0].value)
            temp_row.append(row[1].value)
            temp_row.append(row[2].value)
            if row[2].value >= 18:
                temp_row.append("成年")
            else:
                temp_row.append("未成年")
            sh1.append(temp_row)
        wb.close()
        wb1.save('E:\\temp\\cases_result.xlsx')

    def test_add_image(self):
        xlsx_file = "C:\\Users\\zhangyuming\\Desktop\\公司文件\\gen-code\\a.xlsx"
        image_file = "C:\\Users\\zhangyuming\\Desktop\\公司文件\\压力测试\\测试个人章位置是否正确\\a.png"
        wb1 = openpyxl.Workbook()
        sh1 = wb1.create_sheet('result')
        img = Image(image_file)
        sh1.add_image(img, 'A1')
        wb1.save(xlsx_file)

    # 对比两个excel
    def test_compare_two_excel(self):
        wb1 = openpyxl.load_workbook('C:\\Users\\zhangyuming\\Desktop\\公司文件\\数据处理\\002\\第三方信息采集应用全量数据（截止到当前）-20220829.xlsx')
        wb2 = openpyxl.load_workbook('C:\\Users\\zhangyuming\\Desktop\\公司文件\\数据处理\\002\\已报送.xlsx')
        sh1 = wb1['截止目前向国资委报送的第三方服务机构数据']
        sh2 = wb2['Sheet1']

        # 新保存
        wb3 = openpyxl.Workbook()
        sh3 = wb3.create_sheet('result')

        sh2_contact_ids = []

        print(len(list(sh1.rows)[1:]))
        print(len(list(sh2.rows)[1:]))

        # 声明excel的背景
        orange_fill = PatternFill(fill_type='solid', fgColor="FFC125")

        for row in list(sh2.rows)[1:]:
            sh2_contact_ids.append(row[1].value)

        print(len(sh2_contact_ids))
        print(sh2_contact_ids[1])

        # 跳过第一行头
        i = 1
        for row in list(sh1.rows)[1:]:
            i += 1
            # 只筛选这种数据
            if row[30].value != "自动推送填报单":
                continue
            # 将指定条件的行的列进行颜色标注
            if row[1].value in sh2_contact_ids:
                # 表格的row和column都是从1开始数的
                sh1.cell(row=i, column=2).fill = orange_fill
            else:
                temp_row = []
                temp_row.append(row[1].value)
                sh3.append(temp_row)

        # 测试第三行第三列在哪
        # sh1.cell(row=3, column=3).fill = orange_fill

        wb1.close()
        wb2.close()
        # 将数据保存到另一个表格中, 相当于源文件修改
        wb1.save('C:\\Users\\zhangyuming\\Desktop\\公司文件\\数据处理\\002\\result2.xlsx')
        wb3.save('C:\\Users\\zhangyuming\\Desktop\\公司文件\\数据处理\\002\\result.xlsx')


    """
    单字段分组
    """
    def test_group(self):
        df = pandas.read_excel(r"C:\Users\zhangyuming\Desktop\公司文件\数据处理\003\OCA数据明细.xlsx", sheet_name="Sheet1")
        group_datas = df.groupby("合同大类编码").groups

        for i in group_datas:
            print(i, len(group_datas[i]))

    """
    多字段分组
    """
    def test_group2(self):
        df1 = pandas.read_excel(r"C:\Users\zhangyuming\Desktop\公司文件\数据处理\003\OCA数据明细.xlsx", sheet_name="Sheet1")
        group_datas1 = df1.groupby(["合同大类编码"]).groups
        group_datas2 = df1.groupby(["合同小类编码"]).groups

        dict1 = {}
        dict2 = {}

        df = pandas.read_excel(r"C:\Users\zhangyuming\Desktop\公司文件\数据处理\003\CMS-上线前.xlsx", sheet_name="Sheet1")
        group_datas = df.groupby(["SMALL_CATEGORY"]).groups

        for i in group_datas:
            print(i, len(group_datas[i]))

    def test_modify_contract_state(self):
        # C:\Users\zhangyuming\Desktop\公司文件\temp
        filePath = r"C:\Users\zhangyuming\Desktop\公司文件\temp\a.csv"
        # 读取时会自动跳过第一行
        data = pandas.read_csv(filePath)
        row_list = data.values.tolist()

        if len(row_list) == 0:
            return

        print(f"行读取结果：{type(row_list)}")
        print("总行数: ", len(row_list))

        contract_ids = []

        for item in row_list:
            contract_ids.append(item[0])

        wb1 = openpyxl.load_workbook(r'C:\Users\zhangyuming\Desktop\公司文件\数据处理\003\OCA统计数据表v2.2-修改部分合同状态(1).xlsx')
        sh1 = wb1['明细表']

        # 声明excel的背景
        orange_fill = PatternFill(fill_type='solid', fgColor="FFC125")

        # 跳过第一行头
        i = 1
        for row in list(sh1.rows)[1:]:
            i += 1
            # 只筛选这种数据
            if row[51].value == "审批完毕":
                continue
            # 将指定条件的行的列进行颜色标注
            if row[1].value in contract_ids:
                # 表格的row和column都是从1开始数的
                sh1.cell(row=i, column=51, value="审批完毕").fill = orange_fill


        # 将数据保存到另一个表格中, 相当于源文件修改
        wb1.save('C:\\Users\\zhangyuming\\Desktop\\公司文件\\数据处理\\003\\result3.xlsx')
        wb1.close()

    def test_find_index(self):
        wb1 = openpyxl.load_workbook(r'C:\Users\zhangyuming\Desktop\公司文件\temp\a.xlsx')
        sh1 = wb1['Sheet1']

        for i in range(51):
            sh1.cell(row=1, column=i + 1, value=i + 1)

        sh1.cell(row=2, column=50, value="50")

        wb1.save(r'C:\Users\zhangyuming\Desktop\公司文件\temp\a1.xlsx')
        wb1.close()


    def test_sign_flag(self):
        df = pandas.read_excel(r"C:\Users\zhangyuming\Desktop\公司文件\数据处理\004\第三方信息采集应用全量数据（截止到当前）-20220829.xlsx", sheet_name="截止目前向国资委报送的第三方服务机构数据")

        max_row_count = df.shape[0]
        max_cloumn_count = df.columns.size

        print("最大行:", max_row_count)
        print("最大列:", max_cloumn_count)

        df1 = pandas.read_excel(r"C:\Users\zhangyuming\Desktop\公司文件\数据处理\004\OCA统计数据表v2.1-修改部分合同状态(2)(1)(1)(1)(3)(1).xlsx",
                               sheet_name="明细表")

        max_row_count1 = df1.shape[0]
        max_cloumn_count1 = df1.columns.size

        print("最大行1:", max_row_count1)
        print("最大列1:", max_cloumn_count1)

        contract_ids = []

        for i in range(max_row_count):
            contract_ids.append(df.iloc[i, 2])

        wb1 = openpyxl.load_workbook(r'C:\Users\zhangyuming\Desktop\公司文件\数据处理\004\OCA统计数据表v2.1-修改部分合同状态(2)(1)(1)(1)(3)(1).xlsx')
        sh1 = wb1['明细表']

        print("明细表行数:", len(list(sh1.rows)))

        # 跳过第一行头
        i = 1
        for row in list(sh1.rows)[1:]:
            i += 1
            # 将指定条件的行的列进行颜色标注
            if row[2].value in contract_ids:
                # 表格的row和column都是从1开始数的
                sh1.cell(row=i, column=56, value="已报送")
            else:
                sh1.cell(row=i, column=56, value="未报送")


        # 将数据保存到另一个表格中, 相当于源文件修改
        wb1.save('C:\\Users\\zhangyuming\\Desktop\\公司文件\\数据处理\\004\\result3.xlsx')
        wb1.close()


if __name__ == '__main__':
    unittest.main()
